package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Roll;
import database.DBManager;

public class RollDao {

	private String sqlread   	   = "SELECT * FROM rol";
	private String sqlGetU   	   = "SELECT * FROM rol WHERE rollID = ?";
	private String sqlGetN   	   = "SELECT * FROM rol WHERE rolnaam = ?";
	private String sqlCreate   	   = "INSERT INTO rol(rolnaam) VALUES(?)";
	private String sqlDelete   	   = "DELETE * FROM rol WHERE rollID = ?";
	
	private Connection        con      = null;
	private PreparedStatement psRead   = null;
	private PreparedStatement psGetU   = null;
	private PreparedStatement psGetN   = null;
	private PreparedStatement psCreate = null;
	private PreparedStatement psDelete = null;

	public RollDao(DBManager myDb){
		setStatements(myDb);
	}
	
	private void setStatements(DBManager myDb){
		con = myDb.getConnection();
		try{
			this.psRead   = con.prepareStatement(sqlread);
			this.psGetU   = con.prepareStatement(sqlGetU);
			this.psGetN   = con.prepareStatement(sqlGetN);
			this.psCreate = con.prepareStatement(sqlCreate);
			this.psDelete = con.prepareStatement(sqlDelete);
		} catch(SQLException se) {
			se.printStackTrace();
		}
	}
	
	public ArrayList<Roll> getRolls(){
		ArrayList<Roll> rolls = new ArrayList<Roll>();
		try{
			ResultSet rs = psRead.executeQuery();
			while (rs.next()) {
				int rollID = rs.getInt(1);
				String name = rs.getString(2);
				
				Roll roll = new Roll(rollID, name);
				rolls.add(roll);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return rolls;
	}	
	
	public Roll getRollsByID(int getRollID){
		Roll roll = null;
		try{
			psGetU.clearParameters() ;
			psGetU.setInt(1, getRollID);
			ResultSet rs = psGetU.executeQuery();
			while (rs.next()) {
				int rollID = rs.getInt(1);
				String name = rs.getString(2);
				
				roll = new Roll(rollID, name);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return roll;
	}
	
	public Roll getRollByName(String getName){
		Roll roll = null;
		try{
			psGetN.clearParameters() ;
			psGetN.setString(1, getName);
			ResultSet rs = psGetN.executeQuery();
			while (rs.next()) {
				int rollID = rs.getInt(1);
				String name = rs.getString(2);
				
				roll = new Roll(rollID, name);
			}
			rs.close() ;
		} catch(SQLException se) {
			se.printStackTrace();
		}
		return roll;
	}
	
	
	public boolean createRoll(String name) {
		boolean succeeded = false;
		try {
			psCreate.clearParameters();
			psCreate.setString( 1, name );
			if( psCreate.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
	
	public boolean deleteRoll(int rollID) {
		boolean succeeded = false;
		try {
			psDelete.clearParameters();
			psDelete.setInt( 1, rollID );
			if( psDelete.executeUpdate() != 0 ) {
				succeeded = true;
			}
		} catch( SQLException se ) {
			se.printStackTrace();
		}
		return succeeded;
	}
}
